package dashboard.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.Calendar;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import dashboard.model.Interval;
import dashboard.model.OnDemandReport;
import dashboard.model.Point;
import dashboard.model.RatioType;
import dashboard.model.SnapshotStatResult;
import dashboard.model.StatResult;
import dashboard.statrpt.field.RptField;
import dashboard.statrpt.field.RptInstanceActivityStats;
import dashboard.statrpt.field.RptSnapshotOverview;
import dashboard.statrpt.field.RptSumCacheSizes;
import dashboard.statrpt.field.RptSumInstanceEfficiencyPercentages;
import dashboard.statrpt.field.RptSumSharedPoolStatistics;
import dashboard.utils.FileUtil;
import dashboard.utils.QueryXML;
import dashboard.utils.TransferMap;

/**
 * 
 * @author xu guoneng
 *
 */
public class SnapshotReportService extends DatabaseService{
	
	private static final Log log = LogFactory.getLog(SnapshotReportService.class);
	/**
	 * 
	 * @param beginTime - Begin observation time
	 * @param endTime - End observation time
	 * @param blk_interval_sec - Observation interval, e.g. X or Y value in second
	 * @return SnapshotStatResult which contain a list of points <x-blockId, y-ratio>
	 */
	public SnapshotStatResult getRatio(Timestamp beginTime, Timestamp endTime, long blk_interval_sec){
		try{
			SnapshotStatResult snapshotStatResult = new SnapshotStatResult();
			long dbId = getDbId();
			long interval_ms = blk_interval_sec * 1000;
			long sTime = beginTime.getTime();
			long eTime = endTime.getTime();

			// create intervals in the result
	        while(sTime < eTime){
	        	Interval interval = new Interval();
	            long next_eTime = sTime + interval_ms;
	            next_eTime = next_eTime <= eTime ? next_eTime : eTime;

	            interval.startTime =  new Timestamp(sTime);
	            interval.endTime = new Timestamp(next_eTime);
	            snapshotStatResult.intervals.add(interval);  // add this interval to result 
	                                   
	            sTime = next_eTime + 1;
	        }
	        
	        // foreach value in the enum, we need to get the ratio from database
			for(int i=0; i<RatioType.values().length; i++){
				
				RatioType ratio = RatioType.values()[i];
				log.info(" Getting ratio " + ratio.title + " between " + beginTime + " and " + endTime + " with resolution (s) = " + blk_interval_sec);
				
				StatResult result = new StatResult();
				result.setTitle(ratio.title);

				for(Interval interval : snapshotStatResult.intervals){
					// get the begin and end snapshot ids.
		            long beginSnapshotId = getBeginSnapshotId(interval.startTime);
		            long endSnapshotId = getEndSnapshotId(interval.endTime);
		            
		            Point point = new Point();    // set point x-axis to block id.

		            point.setStart(interval.startTime.getTime());
		            point.setEnd(interval.endTime.getTime());
					switch (ratio) {
						case SharePool:	  
							point.setRatio(getExecuteToParse(beginSnapshotId, endSnapshotId, dbId));
			        		break;
			            case BufferCache: 
			            	point.setRatio(getBufferCacheHitRatio(beginSnapshotId, endSnapshotId, dbId));
			            	break;
			            case RedoLog: 	  
			            	point.setRatio(getRedoNoWaitRatio(beginSnapshotId, endSnapshotId, dbId));
			            	break;
			            case SortMem: 		  
			            	point.setRatio(getSortMemRatio(beginSnapshotId, endSnapshotId, dbId));
			            	break;
			            default:
			            	throw new Exception("Ratio type is not defined: " + ratio);
					}
					
					result.points.add(point);
				}
				snapshotStatResult.results.add(result);
			}
			return snapshotStatResult;
		}
		catch(Exception ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		}
	}
	
	/**
	 * To extract the Summary portion of the detail report plus its full file location
	 * @param beginTime
	 * @param endTime
	 * @return OnDemandReport
	 */
	public OnDemandReport getOnDemandReport(Timestamp beginTime, Timestamp endTime) throws Exception{
		System.out.println("Getting ondemand report from " + beginTime + " to " + endTime);
		
		OnDemandReport report = new OnDemandReport(beginTime, endTime);
		
		String filePath = fetchSnapshotReport(beginTime, endTime);
		report.setReportFilePath(filePath);
		
		if(filePath != null){
			String rpt = FileUtil.readFile(filePath);
			int startIdex = rpt.indexOf("<h1 class=\"awr\">WORKLOAD REPOSITORY report for</h1>");
			int endIdex = rpt.indexOf("<h2 class=\"awr\">Main Report</h2>");
			report.addSubreport(rpt.substring(startIdex, endIdex));
		}
		else{
			report.addSubreport("No information available");
		}
		
		System.out.println("Ondemand report generated from " + beginTime + " to " + endTime);
		
		return report;
	}
	
	private String printHtmlTable(TransferMap map, RptField[] fields, String tableCaption){
		String table = "<table border='1'>";
		table += "<caption><b>" + tableCaption + "</b></caption>";
		table += "<tr><th>Property Name</th><th>Value</th></tr>";
		
		for(RptField f : fields){
			String label = StringEscapeUtils.escapeHtml(f.getLabel());
			Object val = map.get(f);
			table += "<tr><td>" + label + "</td><td>" + val + "</td></tr>";
		}
		
		table += "</table>";
		
		return table;
	}
	
	private double getExecuteToParse(long beginSnapshotId, long endSnapshotId, long dbId){
		try{
			long b_parse_count_total = getStatValue("parse count (total)", beginSnapshotId, dbId);
			long b_parse_count_hard = getStatValue("parse count (hard)", beginSnapshotId, dbId);
			
			long e_parse_count_total = getStatValue("parse count (total)", endSnapshotId, dbId);
			long e_parse_count_hard = getStatValue("parse count (hard)", endSnapshotId, dbId);
			
			return RoundTo2Decimals(
					(1.0 - (e_parse_count_hard - b_parse_count_hard) * 1.0 
					/ (e_parse_count_total - b_parse_count_total)
					) * 100.0
				);
		}catch(Exception e){
			log.error(e.toString());
			return 0;
		}
		
	}
	
	private double getBufferCacheHitRatio(long beginSnapshotId, long endSnapshotId, long dbId){
		try{
			long b_physical_reads_cache = getStatValue("physical reads cache", beginSnapshotId, dbId);
			long b_consistent_gets = getStatValue("consistent gets", beginSnapshotId, dbId);
			long b_db_block_gets = getStatValue("db block gets", beginSnapshotId, dbId);
			
			long e_physical_reads_cache = getStatValue("physical reads cache", endSnapshotId, dbId);
			long e_consistent_gets = getStatValue("consistent gets", endSnapshotId, dbId);
			long e_db_block_gets = getStatValue("db block gets", endSnapshotId, dbId);
			
			return RoundTo2Decimals(
						(1.0 - (e_physical_reads_cache - b_physical_reads_cache) * 1.0 
						/ ((e_consistent_gets - b_consistent_gets) + (e_db_block_gets - b_db_block_gets))
						) * 100.0
					);
		}catch(Exception e){
			log.error(e.toString());
			return 0;
		}
	}
	
	private double getRedoNoWaitRatio(long beginSnapshotId, long endSnapshotId, long dbId){
		try{
			long b_redo_entries = getStatValue("redo entries", beginSnapshotId, dbId);
			long b_redo_log_space_requests = getStatValue("redo log space requests", beginSnapshotId, dbId);
			
			long e_redo_entries = getStatValue("redo entries", endSnapshotId, dbId);
			long e_redo_log_space_requests = getStatValue("redo log space requests", endSnapshotId, dbId);
			
			return RoundTo2Decimals(
						(1.0 - (e_redo_log_space_requests - b_redo_log_space_requests) * 1.0
						/ (e_redo_entries - b_redo_entries) 
						) * 100.0
					);
		}catch(Exception e){
			log.error(e.toString());
			return 0;
		}
	}
	
	private double getSortMemRatio(long beginSnapshotId, long endSnapshotId, long dbId){
		try{
			long b_sorts_rows = getStatValue("sorts (rows)", beginSnapshotId, dbId);
			long b_sorts_memory = getStatValue("sorts (memory)", beginSnapshotId, dbId);
			long b_sorts_disk = getStatValue("sorts (disk)", beginSnapshotId, dbId);
			
			long e_sorts_rows = getStatValue("sorts (rows)", endSnapshotId, dbId);
			long e_sorts_memory = getStatValue("sorts (memory)", endSnapshotId, dbId);
			long e_sorts_disk = getStatValue("sorts (disk)", endSnapshotId, dbId);
			
			return RoundTo2Decimals(
						(1.0 - ((e_sorts_memory - b_sorts_memory) + (e_sorts_disk - b_sorts_disk)) * 1.0
						/ (e_sorts_rows - b_sorts_rows)
						) * 100.0
					);
		}catch(Exception e){
			log.error(e.toString());
			return 0;
		}
	}
	
	double RoundTo2Decimals(double val) {
	    DecimalFormat df2 = new DecimalFormat("###.##");
	    return Double.valueOf(df2.format(val));
	}
	
	private long getStatValue(String name, long snapshotId, long dbId){
		String sql = "select value from dba_hist_sysstat where stat_name = ? and snap_id = ? and dbid = ?";
		return getJdbcTemplate().queryForObject(sql, Long.class, name, snapshotId, dbId);
	}
	
	public long getBeginSnapshotId(java.sql.Timestamp startTime) {
		String sql = "select max(snap_id) from DBA_HIST_SNAPSHOT where end_interval_time <= ? ";
		Long snapshotId = getJdbcTemplate().queryForObject(sql, Long.class, startTime);
		return snapshotId != null ? snapshotId : -1;
	}

	public long getEndSnapshotId(java.sql.Timestamp endTime) {
		String sql = "select min(snap_id) from DBA_HIST_SNAPSHOT where end_interval_time >= ? ";
		Long snapshotId = getJdbcTemplate().queryForObject(sql, Long.class, endTime);
		return snapshotId != null ? snapshotId : -1;
	}
	
	/**
	 * To generate and extract the snapshot report
	 * @param beginTime
	 * @param endTime
	 * @return
	 */
	public TransferMap extractSnapshotReport(Timestamp beginTime, Timestamp endTime){
		try{
			TransferMap report = new TransferMap();

			String filePath = fetchSnapshotReport(beginTime, endTime);
			
			if(filePath != null){
				QueryXML query = new QueryXML(filePath);
				
				//populate selective report statistics
				query.populate(report, RptSnapshotOverview.values());
				query.populate(report, RptSumCacheSizes.values());
				query.populate(report, RptSumInstanceEfficiencyPercentages.values());
				query.populate(report, RptSumSharedPoolStatistics.values());
				query.populate(report, RptInstanceActivityStats.values());
			}
			
			return report;
		}
		catch(Exception ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		}
	}
	
	/**
	 * To generate a new snapshot report if it does not exist
	 * @param beginTime
	 * @param endTime
	 * @return Snapshot report full file name or "null" if instance reset occurs in that period
	 */
	public String fetchSnapshotReport(Timestamp beginTime, Timestamp endTime)
		throws Exception{
		
			long beginSid = getBeginSnapshotId(beginTime);
			long endSid = getEndSnapshotId(endTime);
			
			long dbid = getDbId();
			long instance_id = getInstanceId();
			
			String filePath = constructSnapshotRptFileName(FileUtil.FILE_DIR, instance_id, beginSid, endSid);

			if(!new File(filePath).exists()){
				generateSnapshotReportHtml(dbid, instance_id, beginSid, endSid, filePath);
			}
			else{
				System.out.println("Report has been generated previously at " + filePath);
			}

			return filePath;
	}
	
	protected void generateSnapshotReportHtml(long dbid, long instance_id, long beginSnapshotId, long endSnapshotId, String filePath) throws SQLException {
		String sql = "select output from table(dbms_workload_repository.awr_report_html(?,?,?,?,?))";

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = getDataSource().getConnection();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, dbid);
			ps.setLong(2, instance_id);
			ps.setLong(3, beginSnapshotId);
			ps.setLong(4, endSnapshotId);
			ps.setLong(5, 0);

			rs = ps.executeQuery();

			int nSize = 500000;
			int pos = 0;
			byte[] result = new byte[nSize];
			byte[] temp;
			while (rs.next()) {
				temp = rs.getBytes(1);
				if (temp == null || temp.length <= 0)
					continue;
				if ((pos + temp.length) >= nSize) {
					byte[] origin = result;
					result = new byte[nSize * 2];
					System.arraycopy(origin, 0, result, 0, nSize);
					nSize *= 2;
				}

				System.arraycopy(temp, 0, result, pos, temp.length);
				pos += temp.length;
			}

			byte[] pData = new byte[pos];
			System.arraycopy(result, 0, pData, 0, pos);
			FileOutputStream file = null;
			try {
				file = new FileOutputStream(filePath);
				file.write(pData);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (file != null)
					try {
						file.close();
					} catch (IOException e) {
					}
			}
		} catch (Exception ex) {
			throw new RuntimeException(ex);
		} finally {
			releaseResource(conn, ps, rs);
		}
	}
	
	protected String constructSnapshotRptFileName(String fileDirectory, long instance_id, long bsid, long esid){
		if(bsid < esid){
			return fileDirectory + "awr_report_" + instance_id + "_" + bsid + "_" + esid + ".html";
		}
		else{
			return null;
		}
	}
	
	public void test(){
		Calendar date = Calendar.getInstance();
		date.add(Calendar.HOUR_OF_DAY, -1);
		Timestamp beginTime = new Timestamp(date.getTimeInMillis());
				
		//date.add(Calendar.HOUR_OF_DAY, 1);
		date.add(Calendar.MINUTE, 100);
		Timestamp endTime = new Timestamp(date.getTimeInMillis());
		
		System.out.println("beginSnapshotId: " + getBeginSnapshotId(beginTime) + " at " + beginTime);
		System.out.println("endSnapshotId: " + getEndSnapshotId(endTime) + " at " + endTime);
		
//		System.out.println("TransferMap--------------------------------------------------");
//		TransferMap map = extractSnapshotReport(beginTime, endTime);
//		for(Object key : map.keySet()){
//			System.out.println(key + ": " + map.get(key));
//		}
		
		int interval_sec = 20 * 60;
		//assume observation interval X = 20min
		System.out.println("Execute to Parse--------------------------------------------------");
		SnapshotStatResult result = getRatio(beginTime, endTime, interval_sec);
		
		for(StatResult statResult: result.results){
			System.out.println("-------------" + statResult.title + "------------------");
			for(Point point: statResult.points){
				System.out.println("Point[" + point.getStart() + "," + point.getEnd() + "," + point.getRatio()+ "]");
			}
		}
		
		System.out.println("On demand report--------------------------------------------------");
		try{
			OnDemandReport rpt = getOnDemandReport(beginTime, endTime);
			for(String s : rpt.getHtmlReports()){
				System.out.println(s);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
	}
}
